package com.example.demo.use_demo.a_excel_demo;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.RowUtil;
import cn.hutool.poi.excel.cell.CellEditor;
import cn.hutool.poi.excel.cell.CellUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 必须传入beanType,方式一:从创建Reader时传入(用于数据验证和转换)  方式二：在readExcel的时候传入(用于读取并转换数据)
 * 扩展HuTool添加excel数据读取验证
 *
 * @author zhangqiang
 * @tip converter必须在ruleValidator之前调用
 * @date 2021/3/31 16:09
 */
@Slf4j
public class ExcelReaderExtHuTool<L> extends ExcelReader {
    /**
     * 是否忽略空行
     */
    private boolean ignoreEmptyRow = true;
    /**
     * 单元格值处理接口
     */
    private CellEditor cellEditor;
    /**
     * 标题别名(中文-字段名)
     */
    private Map<String, String> headerAlias = new HashMap<>();
    private Map<String, String> aliasHeader = new HashMap<>();
    /**
     * 验证的表头
     */
    private List<String> validateHeaders;
    /**
     * 验证失败数据的提示
     */
    private List<String> globalErrorList = new ArrayList<>();
    /**
     * 验证失败数据的提示
     */
    private List<String> businessErrorList = new ArrayList<>();
    /**
     * 对象类型
     */
    private Class<L> beanType;
    /**
     * 转换器
     */
    private ExcelDataConverter<L> dataValueConverter;
    /**
     * 验证器
     */
    private ExcelDataValidator<L> businessRuleValidator;
    /**
     * 提示信息是否已转换
     */
    private boolean aliasHeaderConverted = false;

    public ExcelReaderExtHuTool(String excelFilePath, int sheetIndex) {
        super(excelFilePath, sheetIndex);
    }

    public ExcelReaderExtHuTool(File bookFile, int sheetIndex) {
        super(bookFile, sheetIndex);
    }

    public ExcelReaderExtHuTool(File bookFile, String sheetName) {
        super(bookFile, sheetName);
    }

    public ExcelReaderExtHuTool(InputStream bookStream, int sheetIndex, boolean closeAfterRead) {
        super(bookStream, sheetIndex);
    }

    public ExcelReaderExtHuTool(InputStream bookStream, int sheetIndex, boolean closeAfterRead, Class<L> clazz) {
        super(bookStream, sheetIndex);
        this.beanType = clazz;
    }

    public ExcelReaderExtHuTool(InputStream bookStream, String sheetName, boolean closeAfterRead) {
        super(bookStream, sheetName);
    }

    public ExcelReaderExtHuTool(InputStream bookStream, String sheetName, boolean closeAfterRead, Class<L> clazz) {
        super(bookStream, sheetName);
        this.beanType = clazz;
    }

    public ExcelReaderExtHuTool(Workbook book, int sheetIndex) {
        super(book, sheetIndex);
    }

    public ExcelReaderExtHuTool(Workbook book, String sheetName) {
        super(book, sheetName);
    }

    public ExcelReaderExtHuTool(Sheet sheet) {
        super(sheet);
    }

    /**
     * 获取验证器
     */
    @SuppressWarnings("unchecked")
    public ExcelDataValidator<L> businessRuleValidator() {
        this.beanType = beanType == null ? (Class<L>) Map.class : beanType;
        this.businessRuleValidator = this.businessRuleValidator == null ? ExcelDataValidator.newValidator(beanType) : this.businessRuleValidator;
        return businessRuleValidator;
    }

    /**
     * 获取转换器
     */
    @SuppressWarnings("unchecked")
    public ExcelDataConverter<L> dataValueConverter() {
        this.beanType = beanType == null ? (Class<L>) Map.class : beanType;
        this.dataValueConverter = this.dataValueConverter == null ? ExcelDataConverter.newConverter(beanType) : this.dataValueConverter;
        return dataValueConverter;
    }

    /**
     * 设置单元格值处理逻辑<br>
     * 当Excel中的值并不能满足我们的读取要求时，通过传入一个编辑接口，可以对单元格值自定义，例如对数字和日期类型值转换为字符串等
     *
     * @param cellEditor 单元格值处理接口
     * @return this
     */
    @Override
    public ExcelReader setCellEditor(CellEditor cellEditor) {
        this.cellEditor = cellEditor;
        return this;
    }

    /**
     * 设置是否忽略空行
     *
     * @param ignoreEmptyRow 是否忽略空行
     * @return this
     */
    @Override
    public ExcelReader setIgnoreEmptyRow(boolean ignoreEmptyRow) {
        this.ignoreEmptyRow = ignoreEmptyRow;
        return this;
    }

    /**
     * 设置标题行的别名Map
     *
     * @param headerAlias 别名Map
     * @return this
     */
    @Override
    public ExcelReader setHeaderAlias(Map<String, String> headerAlias) {
        this.headerAlias = headerAlias;
        return this;
    }

    /**
     * 设置是否验证表头
     *
     * @param validateHeaders 表头名称列表
     * @return this
     * @editor zhangqiang 2021/5/8 13:48
     */
    public ExcelReader setValidateHeaders(List<String> validateHeaders) {
        this.validateHeaders = validateHeaders;
        return this;
    }

    /**
     * 读取Excel为Bean的列表
     *
     * @param headerRowIndex 标题所在行，如果标题行在读取的内容行中间，这行做为数据将忽略，，从0开始计数
     * @param startRowIndex  起始行（包含，从0开始计数）
     * @return Map的列表
     * @editor zhangqiang 2021/5/8 13:34
     * @since 4.0.1
     */
    public List<L> readExcel(int headerRowIndex, int startRowIndex) {
        this.convertedAliasHeader();
        return this.readExcel(headerRowIndex, startRowIndex, Integer.MAX_VALUE, this.beanType);
    }

    /**
     * 读取Excel为Bean的列表
     *
     * @param headerRowIndex 标题所在行，如果标题行在读取的内容行中间，这行做为数据将忽略，，从0开始计数
     * @param startRowIndex  起始行（包含，从0开始计数）
     * @param beanType       实体类型
     * @return Map的列表
     * @since 4.0.1
     */
    public List<L> readExcel(int headerRowIndex, int startRowIndex, Class<L> beanType) {
        this.convertedAliasHeader();
        return this.readExcel(headerRowIndex, startRowIndex, Integer.MAX_VALUE, beanType);
    }

    /**
     * 读取Excel为Bean的列表
     *
     * @param headerRowIndex 标题所在行，如果标题行在读取的内容行中间，这行做为数据将忽略，，从0开始计数
     * @param startRowIndex  起始行（包含，从0开始计数）
     * @param endRowIndex    读取结束行（包含，从0开始计数）
     * @param beanType       实体类型
     * @return Map的列表
     */
    @SuppressWarnings("unchecked")
    public List<L> readExcel(int headerRowIndex, int startRowIndex, int endRowIndex, Class<L> beanType) {
        this.convertedAliasHeader();
        checkNotClosed();
        // 用于错误提示信息
        String rowIndexKey = RandomStringUtils.random(8);
        final List<Map<String, Object>> mapList = this.readExcel(headerRowIndex, startRowIndex, endRowIndex, rowIndexKey);
        if (Map.class.isAssignableFrom(beanType)) {
            return (List<L>) mapList;
        }
        final List<L> beanList = new ArrayList<>(mapList.size());
        final List<Integer> analyzeErrRowIndexList = new ArrayList<>();
        final List<String> businessValidateErrMessageList = new ArrayList<>();
        String errMessageTmp;
        int rowNumTmp;
        boolean validatorHasCustomFunction = businessRuleValidator != null && businessRuleValidator.hasCustomFunctionValidate();
        boolean converterHasCustomFunction = dataValueConverter != null && dataValueConverter.hasCustomConvertConsumer();
        for (Map<String, Object> map : mapList) {
            rowNumTmp = (Integer) map.get(rowIndexKey) + 1;
            try {
                L t = BeanUtil.mapToBean(map, beanType, false);
                // 自定义行数据转换
                if (converterHasCustomFunction) {
                    dataValueConverter.convertWithCustomConsumer(t);
                }
                // 行数据验证
                boolean validateFailed = false;
                if (validatorHasCustomFunction) {
                    errMessageTmp = businessRuleValidator.validateCustomFunction(t);
                    if (errMessageTmp != null) {
                        validateFailed = true;
                        businessValidateErrMessageList.add(String.format("第%s行:%s", rowNumTmp, errMessageTmp));
                    }
                }
                if (!validateFailed) {
                    beanList.add(t);
                }
            } catch (Exception e) {
                // 行数据解析失败
                analyzeErrRowIndexList.add((Integer) map.get(rowIndexKey) + 1);
            }
        }
        if (!CollectionUtils.isEmpty(analyzeErrRowIndexList)) {
            log.error("数据导入验证,第{}行数据格式错误", analyzeErrRowIndexList.toString());
            globalErrorList.add("第" + analyzeErrRowIndexList.toString() + "行数据格式错误");
        }
        if (!CollectionUtils.isEmpty(businessValidateErrMessageList)) {
            log.error("数据导入验证,第{}行数据验证失败", businessValidateErrMessageList.toString());
            businessErrorList.addAll(businessValidateErrMessageList);
        }
        return beanList;
    }

    /**
     * 读取Excel为Map的列表<br>
     * Map表示一行，标题为key，单元格内容为value
     *
     * @param headerRowIndex 标题所在行，如果标题行在读取的内容行中间，这行做为数据将忽略
     * @param startRowIndex  起始行（包含，从0开始计数）
     * @param endRowIndex    读取结束行（包含，从0开始计数）
     * @param rowIndexKey    行索引数据Key
     * @return Map的列表
     */
    public List<Map<String, Object>> readExcel(int headerRowIndex, int startRowIndex, int endRowIndex, String rowIndexKey) {
        this.convertedAliasHeader();
        checkNotClosed();
        // 边界判断
        final int firstRowNum = sheet.getFirstRowNum();
        final int lastRowNum = sheet.getLastRowNum();
        if (headerRowIndex < firstRowNum) {
            throw new IndexOutOfBoundsException(StrUtil.format("Header row index {} is lower than first row index {}.", headerRowIndex, firstRowNum));
        } else if (headerRowIndex > lastRowNum) {
            throw new IndexOutOfBoundsException(StrUtil.format("Header row index {} is greater than last row index {}.", headerRowIndex, firstRowNum));
        }
        startRowIndex = Math.max(startRowIndex, firstRowNum);// 读取起始行（包含）
        endRowIndex = Math.min(endRowIndex, lastRowNum);// 读取结束行（包含）

        // 读取header
        List<Object> headerList = this.readRow(sheet.getRow(headerRowIndex));
        // 验证header[设置了headers才会验证]
        boolean validateResult = this.validateHeader(headerList);
        if (!validateResult) {
            return Collections.emptyList();
        }

        final List<Map<String, Object>> result = new ArrayList<>(endRowIndex - startRowIndex + 1);
        List<Object> rowList;
        Map<String, Object> rowDataMap;
        for (int i = startRowIndex; i <= endRowIndex; i++) {
            if (i != headerRowIndex) {
                // 跳过标题行
                rowList = this.readRow(sheet.getRow(i));
                if (CollUtil.isNotEmpty(rowList) || !ignoreEmptyRow) {
                    rowDataMap = this.toMap(this.aliasHeader(headerList), rowList, true, rowIndexKey, i);
                    if (rowDataMap != null) {
                        result.add(rowDataMap);
                    }
                }
            }
        }
        return result;
    }

    /**
     * 读取某一行数据
     *
     * @param rowIndex 行号，从0开始
     * @return 一行数据
     * @since 4.0.3
     */
    @Override
    public List<Object> readRow(int rowIndex) {
        return readRow(this.sheet.getRow(rowIndex));
    }

    /**
     * 是否忽略空行
     *
     * @return 是否忽略空行
     */
    @Override
    public boolean isIgnoreEmptyRow() {
        return ignoreEmptyRow;
    }

    /**
     * 读取某个单元格的值
     *
     * @param x X坐标，从0计数，即列号
     * @param y Y坐标，从0计数，即行号
     * @return 值，如果单元格无值返回null
     * @since 4.0.3
     */
    @Override
    public Object readCellValue(int x, int y) {
        return CellUtil.getCellValue(getCell(x, y), this.cellEditor);
    }

    /**
     * 获得标题行的别名Map
     *
     * @return 别名Map
     */
    @Override
    public Map<String, String> getHeaderAlias() {
        return headerAlias;
    }

    /**
     * 增加标题别名
     *
     * @param header 标题
     * @param alias  别名
     * @return this
     */
    @Override
    public ExcelReader addHeaderAlias(String header, String alias) {
        this.headerAlias.put(header, alias);
        return this;
    }

    /**
     * 去除标题别名
     *
     * @param header 标题
     * @return this
     */
    @Override
    public ExcelReader removeHeaderAlias(String header) {
        this.headerAlias.remove(header);
        return this;
    }

    /**
     * 是否存在数据验证错误
     *
     * @return true/false
     */
    public boolean hasErrors() {
        return !CollectionUtils.isEmpty(globalErrorList) || !CollectionUtils.isEmpty(businessErrorList);
    }

    /**
     * 获取全部错误提示
     *
     * @return 错误提示
     */
    public List<String> getAllErrors() {
        globalErrorList.addAll(businessErrorList);
        return globalErrorList;
    }

    /**
     * 获取全局错误提示
     *
     * @return 错误提示
     */
    public List<String> getGlobalErrorList() {
        return globalErrorList;
    }

    /**
     * 获取业务错误提示
     *
     * @return 错误提示
     */
    public List<String> getBusinessErrorList() {
        return businessErrorList;
    }
    //-------------------------------------------------private methods--------------------------------------------------

    /**
     * 转换标题别名，如果没有别名则使用原标题，当标题为空时，列号对应的字母便是header
     *
     * @param headerList 原标题列表
     * @return 转换别名列表
     */
    private List<String> aliasHeader(List<Object> headerList) {
        final int size = headerList.size();
        final ArrayList<String> result = new ArrayList<>(size);
        if (CollUtil.isEmpty(headerList)) {
            return result;
        }

        for (int i = 0; i < size; i++) {
            result.add(aliasHeader(headerList.get(i), i));
        }
        return result;
    }

    /**
     * 转换标题别名，如果没有别名则使用原标题，当标题为空时，列号对应的字母便是header
     *
     * @param headerObj 原标题
     * @param index     标题所在列号，当标题为空时，列号对应的字母便是header
     * @return 转换别名列表
     * @since 4.3.2
     */
    private String aliasHeader(Object headerObj, int index) {
        if (null == headerObj) {
            return ExcelUtil.indexToColName(index);
        }

        final String header = headerObj.toString();
        return ObjectUtil.defaultIfNull(this.headerAlias.get(header), header);
    }

    /**
     * 读取一行
     *
     * @param row 行
     * @return 单元格值列表
     */
    private List<Object> readRow(Row row) {
        return RowUtil.readRow(row, this.cellEditor);
    }

    /**
     * 检查是否未关闭状态
     */
    private void checkNotClosed() {
        Assert.isFalse(this.isClosed, "ExcelReader has been closed!");
    }

    /**
     * 将键列表和值列表转换为Map<br>
     * 以键为准，值与键位置需对应。如果键元素数多于值元素，多余部分值用null代替。<br>
     * 如果值多于键，忽略多余的值。
     *
     * @param <K>         键类型
     * @param <V>         值类型
     * @param keys        键列表
     * @param values      值列表
     * @param isOrder     是否有序
     * @param rowIndex    行号[用于提示异常位置]
     * @param rowIndexKey 行号数据key
     * @return 标题内容Map
     * @since 4.1.12
     */
    public <K extends String, V> Map<K, V> toMap(Iterable<K> keys, Iterable<V> values, boolean isOrder, K rowIndexKey, Integer rowIndex) {
        return this.toMap(null == keys ? null : keys.iterator(), null == values ? null : values.iterator(), isOrder, rowIndexKey, rowIndex);
    }

    /**
     * 将键列表和值列表转换为Map<br>
     * 以键为准，值与键位置需对应。如果键元素数多于值元素，多余部分值用null代替。<br>
     * 如果值多于键，忽略多余的值。
     *
     * @param <K>         键类型
     * @param <V>         值类型
     * @param keys        键列表
     * @param values      值列表
     * @param isOrder     是否有序
     * @param rowIndex    行号[用于提示异常位置]
     * @param rowIndexKey 行号数据key
     * @return 标题内容Map
     * @since 4.1.12
     */
    @SuppressWarnings(value = "unchecked")
    public <K extends String, V> Map<K, V> toMap(Iterator<K> keys, Iterator<V> values, boolean isOrder, K rowIndexKey, Integer rowIndex) {
        final Map<K, V> resultMap = MapUtil.newHashMap(isOrder);
        K keyTmp;
        V valueTmp;
        boolean beforeConvertNotNull;
        String errMessageTmp;
        if (isNotEmpty(keys)) {
            while (keys.hasNext()) {
                keyTmp = keys.next();
                valueTmp = (null != values && values.hasNext()) ? values.next() : null;
                valueTmp = (valueTmp == null) ? null : (StringUtils.isBlank(valueTmp.toString()) ? null : valueTmp);
                beforeConvertNotNull = valueTmp != null;
                valueTmp = (dataValueConverter != null) ? (V) dataValueConverter.convertField(keyTmp, valueTmp) : valueTmp;
                if (dataValueConverter != null && beforeConvertNotNull && valueTmp == null) {
                    this.businessErrorList.add(String.format("第%s行:%s数据不存在", rowIndex + 1, aliasHeader.getOrDefault(keyTmp, keyTmp)));
                    return null;
                }
                errMessageTmp = (businessRuleValidator != null) ? businessRuleValidator.validateField(keyTmp, valueTmp) : null;
                if (StringUtils.isNotBlank(errMessageTmp)) {
                    this.businessErrorList.add(String.format("第%s行:%s", rowIndex + 1, errMessageTmp.replace(keyTmp, aliasHeader.getOrDefault(keyTmp, keyTmp))));
                    return null;
                } else {
                    resultMap.put(keyTmp, valueTmp);
                }
            }
            // 非业务数据处理[用于数据转换错误提示]
            resultMap.put(rowIndexKey, (V) rowIndex);

            errMessageTmp = (businessRuleValidator != null && businessRuleValidator.hasUnionRepeatValidate()) ? businessRuleValidator.validateUnionRepeat(resultMap) : null;
            if (errMessageTmp != null) {
                String replace = StringUtils.replaceEach(errMessageTmp, aliasHeader.keySet().toArray(new String[]{}), aliasHeader.values().toArray(new String[]{}));
                this.businessErrorList.add(String.format("第%s行:%s", rowIndex + 1, replace) + "数据重复");
                return null;
            }
        }
        return resultMap;
    }

    /**
     * Iterator是否为空
     *
     * @param iterator Iterator对象
     * @return 是否为空
     */
    public static boolean isNotEmpty(Iterator<?> iterator) {
        return null != iterator && iterator.hasNext();
    }

    /**
     * 提示信息转换
     */
    private void convertedAliasHeader() {
        if (this.aliasHeaderConverted) {
            return;
        }
        // 用于错误提示信息
        this.headerAlias.forEach((k, v) -> this.aliasHeader.put(v, k));
        this.aliasHeaderConverted = true;
    }

    /**
     * 验证表头
     *
     * @param headerList 表头列表
     * @return true/false
     */
    private boolean validateHeader(List<Object> headerList) {
        if (CollectionUtil.isEmpty(this.validateHeaders)) {
            return true;
        }
        if (!CollectionUtils.isEmpty(headerList)) {
            List<String> notFoundHeaders = validateHeaders.stream().filter(item -> !headerList.contains(item)).collect(Collectors.toList());
            if (CollectionUtil.isEmpty(notFoundHeaders)) {
                return true;
            }
            this.globalErrorList.add("表头验证失败!未找到" + notFoundHeaders.toString());
            return false;
        } else {
            this.globalErrorList.add("表头验证失败!未找到" + validateHeaders.toString());
            return false;
        }
    }
}
